[Notes] MySQL Basic 2

Posted on | In
Symbols count in article: 9.1k | Reading time ≈ 8 mins.

Save search results to database

  1. Under db.mysql package, create class/MySQLConnection.java.
  2. Implement DBConnection interface
  3. Implement both close method and constructor.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
// connection
private Connection conn;

// constructor
public MySQLConnection() {
    try {
        Class.forName("com.mysql.jdbc.Driver").newInstance();
        conn = DriverManager.getConnection(MySQLDBUtil.URL);
    } catch (Exception e) {
        e.printStackTrace();
    }
}

/**
    * close the connection
    */
@Override
public void close() {
    if (conn != null) {
        try {
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}
  1. implement searchItems() in MySQLConnection. Previously we call TicketMasterAPI.search from our SearchItem servlet directly. But actually our recommendation code also needs to call the same search function, so we make a designated function here to do the search call.
    The code is simply copied from what we’ve already had in SearchItem.java.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
/**
    * Search Item. Same as TMAPI search item.
    */
@Override
public List<Item> searchItems(double lat, double lon, String term) {
    // Get the items from TM
    TicketMasterAPI tmAPI = new TicketMasterAPI();
    List<Item> items = tmAPI.search(lat, lon, term);
    // Save the items to the db
    for (Item item: items) {
        saveItem(item);
    }
    return items;
}
  1. after searchItem, let’s try saveItem to save data into database. Again, careful with the import suggestions. Always choose java.sql.*.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
/**
    * Save single item into db
    */
@Override
public void saveItem(Item item) {
    if (conn == null) {
        return;
    }
    try {
        String sql = "INSERT IGNORE INTO items VALUES (?, ?, ?, ?, ?, ?, ?)";
        PreparedStatement stmt = conn.prepareStatement(sql);
        // Index starts from 1
        stmt.setString(1, item.getItemId());
        stmt.setString(2, item.getName());
        stmt.setDouble(3, item.getRating());
        stmt.setString(4, item.getAddress());
        stmt.setString(5, item.getImageURL());
        stmt.setString(6, item.getUrl());
        stmt.setDouble(7,  item.getDistance());
        stmt.execute();
        
        // set categories
        sql = "INSERT IGNORE INTO categories VALUES(?, ?)";
        stmt = conn.prepareStatement(sql);
        for (String category: item.getCategories()) {
            stmt.setString(1, item.getItemId());
            stmt.setString(2, category);
            stmt.execute();
        }

    } catch (SQLException e) {
        e.printStackTrace();
    }
    
}

Use PreparedStatement and stmt.settring() can effectively avoid SQL injection.

PreparedStatement is faster than raw String. Only have to create it once.

  1. SQL injection. Turns the input to the SQL statement, and makes the query always true.
1
2
3
4
5
6
7
8
9
10
11
12

// SQL injection
// Example:
// SELECT * FROM users WHERE username = '<username>' AND password = '<password>';
//
// sql = "SELECT * FROM users WHERE username = '" + username + "'
//       AND password = '" + password + "'"
//
// username: aoweifjoawefijwaoeifj
// password: 123456' OR '1' = '1
//
// SELECT * FROM users WHERE username = 'aoweifjoawefijwaoeifj' AND password = '123456' OR '1' = '1'
  1. update DBConnectionFactory.
1
2
3
4
5
6
7
8
9
10
11
public static DBConnection getConnection(String db) {
    switch (db) {
    case "mysql":
        return new MySQLConnection();
    case "mongodb":
        // return new MongoDBConnection();
        return null;
    default:
        throw new IllegalArgumentException("Invalid db: " + db);
    }
}
  1. In src/rpc/SearchItem.java, add a private dbconnection field and update doGet().
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
/**
    * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
    */
protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    // TODO Auto-generated method stub
    
    double lat = Double.parseDouble(request.getParameter("lat"));
    double lon = Double.parseDouble(request.getParameter("lon"));
    
    String keyword = request.getParameter("term");
    
    // Connect db first then search
    DBConnection conn = DBConnectionFactory.getConnection();
    List<Item> items = conn.searchItems(lat, lon, keyword);
    
    JSONArray array = new JSONArray();
    try {
        for (Item item : items) {
            JSONObject obj = item.toJSONObject();
            array.put(obj);
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    RpcHelper.writeJSONArray(response, array);
}

Implement set/unset favorite related functions

  1. let’s try setFavoriteItem and unsetFavoriteItem
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
/**
    * Operate on History table.
    * insert favorite information.
    */
@Override
public void setFavoriteItems(String userId, List<String> itemIds) {
    if (conn == null) {
        return;
    }
    try {
        String sql = "INSERT IGNORE INTO history (?, ?)";
        PreparedStatement stmt = conn.prepareStatement(sql);
        for (String itemId: itemIds) {
            stmt.setString(1, userId);
            stmt.setString(2, itemId);
            stmt.execute();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }
}

/**
    * Delete favorite information
    */
@Override
public void unsetFavoriteItems(String userId, List<String> itemIds) {
    if (conn == null) {
        return;
    }
    try {
        String sql = "DELETE FROM history WHERE user_id = ? AND item_id = ?";
        PreparedStatement stmt = conn.prepareStatement(sql);
        for (String itemId: itemIds) {
            stmt.setString(1, userId);
            stmt.setString(2, itemId);
            stmt.execute();
        }
    } catch (SQLException e) {
        e.printStackTrace();
    }		
}

  1. create a new servlet called ItemHistory, update the url mapping to \history

  2. create a new function in RpcHelper.java to parse HTTP request body. Imagine the input HTTP request looks like:

1
2
3
4
5
6
7
{
    user_id = “1111”,
    favorite = [
        “abcd”,
        “efgh”,
    ]
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
/**
    * Read the http request and parse it as a JSONObject.
    * @param request
    * @return
    */
public static JSONObject readJsonObject(HttpServletRequest request) {
    StringBuilder sb = new StringBuilder();
    try {
        BufferedReader reader = request.getReader();
        String line = null;
        while ((line = reader.readLine()) != null) {
            sb.append(line);
        }
        reader.close();
        return new JSONObject(sb.toString());
    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}
  1. update doPost() and doDelete in ItemHistory.java to use this new function. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
/**
    * Get the set favourite request and do the corresponding operation
    */
protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
    try {
        // get userId, itemIDs from request
        JSONObject input = RpcHelper.readJsonObject(request);
        String userId = input.getString("user_id");
        JSONArray fav_array = input.getJSONArray("favorite");
        List<String> itemIds = new ArrayList<>();
        for (int i = 0; i < fav_array.length(); i++) {
            itemIds.add(fav_array.get(i).toString());
        }
        
        // connect db and update the table
        DBConnection conn = DBConnectionFactory.getConnection();
        conn.setFavoriteItems(userId, itemIds);
        conn.close();
        RpcHelper.writeJSONObject(response, new JSONObject().put("result", "SUCCESS"));
        
    } catch (Exception e) {
        e.printStackTrace();
    }
}

/**
* Same as doPost(), but change setFavoriteItems() to unsetFavoriteItems()
*/
protected void doDelete(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
  1. open postman, switch to post method, use http://localhost:8080/Jupiter/history, then copy the following JSON object into body. Replace item_id1 and item_id2 with the real item_id exist in your item table.
1
2
3
4
5
6
7
{
     'user_id':'1111',
     'favorite' : [
         'item_id1',
         'item_id2'
     ]
}
  1. now let’s send another request to test our delete function. Open another tab in postman, switch method to delete, use http://localhost:8080/Jupiter/history, then copy the following JSON object into body. Again replace item_id1 with the real item_id exist in your history table.
    1
    2
    3
    4
    5
    6
    {
         'user_id':'1111',
         'favorite' : [
             'item_id1',
         ]
    }